#!/usr/bin/env python
'''PyLibrarian database helper functions'''

import ConfigParser
from datetime import datetime, timedelta
from sqlalchemy.sql.functions import count
from sqlalchemy.sql.expression import or_
from hashlib import sha256
import model

config = ConfigParser.ConfigParser()
config.readfp(open('config.ini'))

checkOutTime = config.get('library', 'checkout_time')

# Database utility functions
def addUser(name, username=None, password=None, street=None, city=None,
            state=None, zip=None, phone=None, email=None):
  '''Adds a user to the library database'''
  session = model.session()
  try:
    if not username:
      username = ''.join([c.lower() for c in name if c.isalpha()])
    user = model.User(name=name, username=username, password=sha256(password).hexdigest(), street=street, city=city,
                      state=state, zip=zip if str(zip).isdigit() else None, phone=phone, email=email)
    session.add(user)
    session.commit()
  finally:
    session.close()

def updateUser(id, name, username=None, password=None, street=None, city=None,
               state=None, zip=None, phone=None, email=None):
  '''Adds a user to the library database'''
  session = model.session()
  try:
    if not username:
      username = ''.join([c.lower() for c in name if c.isalpha()])
    
    user = session.query(model.User).filter_by(id=id).first()
    
    if user:
      if password:
        user.password = sha256(password).hexdigest()
      
      user.name = name
      user.username = username
      user.street = street
      user.city = city
      user.state = state
      user.zip = zip if str(zip).isdigit() else None
      user.phone = phone
      user.email = email
      
      session.add(user)
      session.commit()
  finally:
    session.close()
    
def deleteUser(id):
  '''Removes a user from the system'''
  session = model.session()
  try:
    user = session.query(model.User).filter_by(id=id).first()
    
    if user:
      session.delete(user)
      session.commit()
  finally:
    session.close()

def getUsers():
  '''Returns a list of users'''
  session = model.session()
  try:
    users = []
    for user in session.query(model.User).order_by(model.User.id.asc()).all():
      rec = user.__dict__
      del rec['password']
      users.append(rec)
    return users
  finally:
    session.close()

def getUserByID(id):
  '''Gets the user with the given ID'''
  session = model.session()
  try:
    result = session.query(model.User).filter_by(id=id).first()
    return result.__dict__ if result else {}
  finally:
    session.close()

def checkLogin(username, password):
  '''Validates a login attempt. Returns the user object on success, None upon failure'''
  session = model.session()
  try:
    return session.query(model.User).filter_by(username=username).filter_by(password=sha256(password).hexdigest()).first()
  finally:
    session.close()

def getResourceByID(id):
  '''Gets the resource with the given ID'''
  session = model.session()
  try:
    result = session.query(model.Resource).filter_by(id=id).first()
    return result.__dict__ if result else None
  finally:
    session.close()

def getResourcesByISBN(isbn):
  '''Gets all resources with the given ISBN'''
  session = model.session()
  try:
    results = session.query(model.Resource).filter_by(isbn=isbn).all()
    return [res.__dict__ for res in results]
  finally:
    session.close()
    
def getAvailableResourceByISBN(isbn):
  '''Gets the first available resource with the given ISBN.
     This is called if the user scans an ISBN instead of a resource-specific barcode.'''
  session = model.session()
  try:
    qry = session.query(model.Resource).filter_by(isbn=isbn).all()
    results = [res.__dict__ for res in qry if isResourceCheckedIn(res.id)]
    return results[0] if len(results) else {}
  finally:
    session.close()

def getResources():
  '''Returns a list of all resources'''
  session = model.session()
  try:
    return [resource.__dict__ for resource in session.query(model.Resource).order_by(model.Resource.id.desc()).all()]
  finally:
    session.close()
    
def getPopularResources():
  '''Returns a list of the top 20 most borrowed resources'''
  session = model.session()
  try:
    results = session.query(model.Resource).join(model.Loan).\
                            group_by(model.Resource.isbn).group_by(model.Resource.id).group_by(model.Resource.title).group_by(model.Resource.author).group_by(model.Resource.publisher).group_by(model.Resource.resource_type_id).group_by(model.Resource.summary).order_by(count(model.Resource.isbn).desc())[:20]
    return [result.__dict__ for result in results]
  finally:
    session.close()

def getCheckedOutResources(user_id=None, overdue=False):
  '''Returns a list of resources that are checked, optionally filtered by
     user id and overdue status'''
  session = model.session()
  try:
    # Define the value endOfDay to be the last possible microsecond of today for determining overdue status
    endOfDay = datetime.now()
    endOfDay = endOfDay.replace(hour=11, minute=59, second=59, microsecond=999)
    
    query = session.query(model.Resource.id,
                          model.Resource.title,
                          model.Resource.resource_type_id,
                          model.Loan.due_date,
                          model.User.name,
                          model.User.email).join(model.Loan).\
                                            join(model.User).\
                                            filter(model.Loan.returned_date==None)
    
    if overdue:   # Optionally only return overdue resources
      query = query.filter(model.Loan.due_date < endOfDay)
    
    if user_id:
      query = query.filter(model.Loan.user_id==user_id)
      
    return [{'id': result[0],
             'title': result[1],
             'resource_type_id': result[2],
             'due_date': result[3],
             'card_name': result[4],
             'card_email': result[5],
             'overdue': result[3] < endOfDay} for result in query.all()]
  finally:
    session.close()

def searchResources(search):
  '''Returns a list of resources matching the search string'''
  session = model.session()
  try:
    searchNum = int(search) if search.isdigit() else None
    search = '%%%s%%' % search
    return session.query(model.Resource)\
                  .filter(or_(model.Resource.title.ilike(search),
                              model.Resource.author.ilike(search),
                              model.Resource.publisher.ilike(search),
                              model.Resource.summary.ilike(search),
                              model.Resource.isbn == searchNum,
                              model.Resource.id == searchNum))
  finally:
    session.close()

def getResourceTypes():
  '''Returns a list of all resource types'''
  session = model.session()
  try:
    return [resource.__dict__ for resource in session.query(model.ResourceType).all()]
  finally:
    session.close()

def addResource(isbn, resourceType, title, author, publisher=None, summary=None):
  '''Adds a resource to the library database'''
  session = model.session()
  try:
    resource = model.Resource(isbn=isbn, resource_type_id=resourceType, title=title,
                              author=author, publisher=publisher, summary=summary)
    session.add(resource)
    session.commit()
  finally:
    session.close()
  
def updateResource(id, resourceType, title, author, publisher=None, summary=None):
  session = model.session()
  try:
    resource = session.query(model.Resource).filter_by(id=id).first()
    
    if resource:
      resource.title = title
      resource.author = author
      resource.publisher = publisher
      resource.resource_type_id = resourceType
      resource.summary = summary
      session.add(resource)
      
      session.commit()
  finally:
    session.close()
  
def updateResourcesByISBN(isbn, resourceType, title, author, publisher=None, summary=None):
  session = model.session()
  try:
    resources = session.query(model.Resource).filter_by(isbn=isbn).all()
    
    for resource in resources:
      resource.title = title
      resource.author = author
      resource.publisher = publisher
      resource.resource_type_id = resourceType
      resource.summary = summary
      session.add(resource)
      
    session.commit()
  finally:
    session.close()
    
def deleteResourceByID(id):
  session = model.session()
  try:
    resource = session.query(model.Resource).filter_by(id=id).first()
    if resource:
      session.delete(resource)
      session.commit()
  finally:
    session.close()
  
def checkoutResource(resource_id, user_id):
  '''Marks a resource as checked out by a user'''
  if not isResourceCheckedIn(resource_id) or not resource_id or not user_id:
    return False     # already checked out
  
  session = model.session()
  try:
    loan = model.Loan(resource_id=resource_id, user_id=user_id, due_date=datetime.now() + timedelta(weeks=3))
    session.add(loan)
    session.commit()
    return True
  except:
    return False
  finally:
    session.close()
  
def checkinResource(resource_id):
  '''Marks a resource as checked in'''
  if isResourceCheckedIn(resource_id) or not resource_id:
    return False      # already checked in or not enough info
  
  session = model.session()
  try:
    loans = session.query(model.Loan).filter_by(resource_id=resource_id).filter_by(returned_date=None).all()
    for loan in loans:
      loan.returned_date = datetime.now()
      session.add(loan)
    session.commit()
    return True
  except:
    return False
  finally:
    session.close()
  
def getISBNCheckedOutByUser(isbn, user_id):
  '''Returns the loan information (if any) for a given ISBN and checked out by the given user'''
  session = model.session()
  try:
    result = session.query(model.Loan).join(model.Resource).filter(model.Resource.isbn==isbn).filter(model.Loan.user_id==user_id).filter(model.Loan.returned_date==None).first()
    return result.__dict__ if result else {}
  finally:
    session.close()
  
def isResourceCheckedIn(id):
  '''Returns whether a resource is checked in or not'''
  session = model.session()
  try:
    return session.query(model.Loan).filter_by(resource_id=id).filter_by(returned_date=None).count() == 0
  finally:
    session.close()
  
def reserveResource(resource_isbn, user_id):
  '''Reserves a resource for a user'''
  
  raise NotImplementedError('Book resource reservations have not yet been implemented')
  
  session = model.session()
  try:
    hold = model.Hold(resource_isbn=resource_isbn, user_id=user_id)
    session.add(hold)
    session.commit()
  finally:
    session.close()